package com.iris.live.services.data.repositories.impl;

import com.google.common.base.Strings;
import com.google.common.collect.Iterables;
import com.google.common.collect.Maps;
import com.iris.live.services.data.repositories.ApiForLiveRepository;
import com.iris.live.services.models.PageData;
import com.iris.live.services.models.ResultData;
import com.iris.live.services.services.ApiForLiveService;
import com.iris.live.services.services.CustomerInfoService;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.util.*;

/**
 *
 */
@Repository
@Transactional
public class ApiForLiveRepositoryImpl extends BaseRepository implements ApiForLiveRepository {


    /**
     * 获取本店，选取角色可使用功能
     */
    @Override
    public List getAvailablePermissions(String permit, String roleName) {
        String sql = "SELECT C.itemName from itemInfo AS C,(SELECT A.itemId FROM roleItem AS A WHERE A.permit =:permit \n" +
                "AND roleId = (SELECT `角色编号` FROM `LSH角色表` AS B where `角色` =:roleName)) AS D WHERE C.id=D.itemId";
        Map map = Maps.newHashMap();
        map.put("permit", permit);
        map.put("roleName", roleName);
        return this.getList(sql, map);
    }

    /**
     * 获取本店可可以接待人员
     */
    @Override
    public List getAvailableUser(String permit) {
        String sql = "select 用户名,人员姓名 FROM 用户 WHERE 许可=:permit " +
                "AND IFNULL(删除状态,'') <> '已删除' AND IFNULL(销售顾问职位状态,'') <> '离职' AND 接待状态 = '是'";
        Map<String, String> map = Maps.newHashMap();
        map.put("permit", permit);
        return this.getList(sql, map);
    }

    @Override
    public List getClueAboutSC(String permit, String scCode) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT ");
        sb.append(" 	`电话号码`,  ");
        sb.append(" 	`顾客姓名`, ");
        sb.append(" 	`新车销售状态`, ");
        sb.append(" 	concat('1-',`顾客编号`) 顾客编号 ");
        sb.append(" FROM `顾客记录汇总` ");
        sb.append(" WHERE `顾客记录汇总`.`许可` = :permit ");
        sb.append(" AND IFNULL(`顾客记录汇总`.`顾客状态`,'') <> '战败' ");
        sb.append(" AND `顾客记录汇总`.`所属销售顾问` = :scCode ");
        sb.append(" UNION ALL  ");
        sb.append(" SELECT  ");
        sb.append(" 	`电话号码` ,  ");
        sb.append(" 	`顾客姓名` , ");
        sb.append(" 	`新车销售状态` , ");
        sb.append(" 	concat('2-',`导入编号`) 顾客编号 ");
        sb.append(" FROM `lsh导入名单`   ");
        sb.append(" WHERE `许可` = :permit   ");
        sb.append(" AND `所属销售顾问` = :scCode   ");
        sb.append(" AND `是否线索` = '是'   ");
        sb.append(" AND `是否有效线索` = '是'   ");
        sb.append(" AND `线索创建日期` >= date_sub( date_sub( date_format(now(), '%y-%m-%d'), INTERVAL extract(DAY FROM now()) - 1 DAY ), INTERVAL 2 MONTH )  ");
        sb.append(" AND `电话号码` NOT IN (SELECT `电话号码` from `顾客记录汇总` WHERE `许可` = :permit) ");
        Map<String, String> map = Maps.newHashMap();
        map.put("permit", permit);
        map.put("scCode", scCode);
        return this.getList(sb.toString(), map);
    }

    @Override
    public List getAvailableUsers(String permit, String status) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT  ");
        sb.append(" 	用户名,  ");
        sb.append(" 	人员姓名,  ");
        sb.append(" 	IFNULL(a.`未订单线索数`,'0') 未订单线索数   ");
        sb.append(" FROM 用户  ");
        sb.append(" LEFT JOIN (  ");
        sb.append(" 	SELECT   ");
        sb.append(" 		`所属销售顾问`,  ");
        sb.append(" 		COUNT(`所属销售顾问`) 未订单线索数  ");
        sb.append(" 	FROM (  ");
        sb.append(" 		SELECT ");
        sb.append(" 			`电话号码`,  ");
        sb.append(" 			`所属销售顾问`   ");
        sb.append(" 		FROM `顾客记录汇总` ");
        sb.append(" 		WHERE `顾客记录汇总`.`许可` = :permit ");
        sb.append(" 		AND IFNULL(`顾客记录汇总`.`顾客状态`,'') <> '战败' ");
        sb.append(" 		AND `顾客记录汇总`.`所属销售顾问` <> '' ");
        sb.append(" 		AND `顾客记录汇总`.`所属销售顾问` IS NOT NULL ");
        sb.append(" 		AND `顾客记录汇总`.`是否订单` = '否' ");
        sb.append(" 		AND `顾客记录汇总`.`是否交车` = '否' ");
        //sb.append(" 		AND `顾客记录汇总`.`是否报价` = '否' ");
        sb.append(" 		UNION ALL  ");
        sb.append(" 		SELECT  ");
        sb.append(" 			`电话号码`,  ");
        sb.append(" 			`所属销售顾问`  ");
        sb.append(" 		FROM `lsh导入名单`   ");
        sb.append(" 		WHERE `许可` = :permit   ");
        sb.append(" 		AND `所属销售顾问` IS NOT NULL   ");
        sb.append(" 		AND `所属销售顾问` <> ''   ");
        sb.append(" 		AND `是否线索` = '是'   ");
        sb.append(" 		AND `是否有效线索` = '是'   ");
        sb.append(" 		AND `线索创建日期` >= date_sub(curdate(),interval 3 MONTH)  ");
        sb.append(" 		AND `电话号码` NOT IN (SELECT `电话号码` from `顾客记录汇总` WHERE `许可` = :permit)  ");
        sb.append(" 	) a GROUP BY a.`所属销售顾问`  ");
        sb.append(" ) a ON a.`所属销售顾问` = `用户`.`用户名`  ");
        sb.append(" WHERE 许可 =:permit ");
        sb.append(" AND IFNULL(删除状态, '') <> '已删除'  ");
        sb.append(" AND 销售顾问职位状态 = :status ");
        sb.append(" AND 角色 = '销售顾问' ");
        Map<String, String> map = Maps.newHashMap();
        map.put("permit", permit);
        map.put("status", status);
        return this.getList(sb.toString(), map);
    }

    /**
     * 获取车型详细不为空的LSH车型list
     */
    @Override
    public List getLshCarNotBlank() {
        String sql = "select a.车型编号,a.品牌,a.来源,a.级别,a.车型,a.车型详细,a.Variant," +
                "a.Type,a.Build_date from LSH车型 a where a.车型详细 <> ''";
        return this.getList(sql);
    }

    /**
     * 根据许可 查询未删除的经销商
     */
    public List getLshDistributorByPermit(String permit) {
        String sql = "select * from LSH经销商表  WHERE 许可_BMBS=:permit and  " +
                " IFNULL(经销商状态,'') <> '已删除'";
        Map<String, String> map = Maps.newHashMap();
        map.put("permit", permit);
        return this.getList(sql, map);
    }

    /**
     * 根据车架号查询到GP1、GP2、GP3
     */
    public Map getGPByVin(String vin) {
        Map gpMap = new HashMap();
        String sql = "select DISTINCT  " +
                "		(不含税价 - 采购价格未税) AS GP1,(不含税价 - 采购价格未税) / (不含税价) AS GP1Rate,    " +
                "		  " +
                "		               不含税价 - 采购价格未税 - 经销商赠送成本 - 厂家随车SI成本 + si_adjust -    " +
                "                	((精品成本 + after_market_product_cost_adjust) +   " +
                "									(延保原价 + five_stars_extend_cost_adjust))-经销商采购价未税 AS GP2,  " +
                "  " +
                "		               (不含税价 - 采购价格未税 - 经销商赠送成本 - 厂家随车SI成本 + si_adjust -    " +
                "                	((精品成本 + after_market_product_cost_adjust) +   " +
                "									(延保原价 + five_stars_extend_cost_adjust))-经销商采购价未税)/不含税价 AS GP2Rate,  " +
                "  " +
                "		               不含税价 - 采购价格未税 - 经销商赠送成本 - 厂家随车SI成本 + si_adjust -    " +
                "                	((精品成本 + after_market_product_cost_adjust) +   " +
                "									(延保原价 + five_stars_extend_cost_adjust))-经销商采购价未税 +厂家返利+零售SI+批发SI AS GP3,  " +
                "									  " +
                "		               (不含税价 - 采购价格未税 - 经销商赠送成本 - 厂家随车SI成本 + si_adjust -    " +
                "                	((精品成本 + after_market_product_cost_adjust) +   " +
                "									(延保原价 + five_stars_extend_cost_adjust))-经销商采购价未税 +厂家返利+零售SI+批发SI)/不含税价 AS GP3Rate,  " +
                "								  " +
                "									 不含税价 - 采购价格未税 - 经销商赠送成本 - 厂家随车SI成本 + si_adjust -    " +
                "                	((精品成本 + after_market_product_cost_adjust) +   " +
                "									(延保原价 + five_stars_extend_cost_adjust))-经销商采购价未税 +厂家返利+零售SI+批发SI+  " +
                "                	折后价-成本 + 其他服务费 +上牌服务费+ (五星毛利 + five_stars_extend_cost_adjust) +    " +
                "                	金融服务费+ (金融手续费 + finance_poundage_adjust) +  " +
                "									(交强险手续费 + 商业险手续费 + insurance_poundage_adjust) + 置换评估费 AS SalesTGP,  " +
                "									  " +
                "									 (不含税价 - 采购价格未税 - 经销商赠送成本 - 厂家随车SI成本 + si_adjust -    " +
                "                	((精品成本 + after_market_product_cost_adjust) +   " +
                "									(延保原价 + five_stars_extend_cost_adjust))-经销商采购价未税 +厂家返利+零售SI+批发SI +  " +
                "                	折后价-成本 + 其他服务费 +上牌服务费+ (五星毛利 + five_stars_extend_cost_adjust) +    " +
                "                	金融服务费+ (金融手续费 + finance_poundage_adjust) +  " +
                "									(交强险手续费 + 商业险手续费 + insurance_poundage_adjust) + 置换评估费)/  " +
                "									(不含税价+折后价 + 其他服务费 +上牌服务费+ (五星毛利 + five_stars_extend_cost_adjust) +    " +
                "                	金融服务费+ (金融手续费 + finance_poundage_adjust) +(交强险手续费 + 商业险手续费 + insurance_poundage_adjust) + 置换评估费) as SalesTGPRate  " +
                "  " +
                "  " +
                "			from(  " +
                "							SELECT    " +
                "                		IFNULL(a.销售价格,0) as 销售价格,  " +
                "										IFNULL(a.不含税价,0) as 不含税价,    " +
                "                		IFNULL(b.采购价格未税,0) as 采购价格未税,  " +
                "										IFNULL(b.`采购价格含税`,0) as 采购价格含税 ,  " +
                "										IFNULL(d.经销商赠送成本,0) as 经销商赠送成本,    " +
                "                		IFNULL(e.厂家随车SI成本,0) as 厂家随车SI成本,    " +
                "                		0 AS 延保折后价,  " +
                "										IFNULL(g.延保原价,0) as 延保原价,    " +
                "                		IFNULL(h.其他服务费,0)/1.06 as 其他服务费, " +
                "                		IFNULL(i.金融服务费,0)/1.06 AS 金融服务费,    " +
                "                		IFNULL(i.金融手续费,0)/1.17 AS 金融手续费,    " +
                "                		IFNULL(j.交强险手续费,0)/1.17 AS 交强险手续费,    " +
                "                		IFNULL(j.商业险手续费,0)/1.17 AS 商业险手续费,    " +
                "                		IFNULL(k.置换评估费,0)/1.17 AS 置换评估费,    " +
                "                		0 AS 精品成本,    " +
                "                		IFNULL(l.折后价,0)/1.17 as 折后价,  " +
                "						IFNULL(l.成本,0)/1.17 as 成本,  " +
                "                		IFNULL(r.si_adjust,0) as si_adjust,    " +
                "                		IFNULL(r.finance_poundage_adjust,0) as finance_poundage_adjust,    " +
                "                		IFNULL(r.insurance_poundage_adjust,0) as insurance_poundage_adjust,    " +
                "                		IFNULL(r.after_market_product_cost_adjust,0) as after_market_product_cost_adjust,    " +
                "                		IFNULL(r.five_stars_extend_cost_adjust,0) as five_stars_extend_cost_adjust,    " +
                "                		IFNULL(m.厂家返利,0) as 厂家返利,  " +
                "                		IFNULL(n.五星毛利,0) as 五星毛利,    " +
                "                		IFNULL(v.经销商采购价未税,0) as 经销商采购价未税,  " +
                "										IFNULL(BMBS_retail_SI,0) AS 零售SI,IFNULL(BMBS_wholesale_SI,0) AS `批发SI`,  " +
                "										ss.carCode,  " +
                "										IFNULL(hh.上牌服务费,0)/1.06 as 上牌服务费  " +
                "                	FROM 报价交车表 a    " +
                "                	JOIN (  " +
                "												SELECT a.*,c.采购价格未税,c.采购价格含税  " +
                "												from 报价单表 a, (  " +
                "												select 初始报价单号, max(报价编号) 报价编号  " +
                "												from 报价单表  " +
                "												where 初始报价单号 in (SELECT b.初始报价单号  " +
                "																							from `lsh库存表` a, 报价单表 b  " +
                "																							where a.报价编号 = b.报价编号 and 车架号=:vin)  " +
                "												group by 初始报价单号) b, lsh库存表 c  " +
                "												where a.报价编号 = b.报价编号 and a.库存编号 = c.库存编号  " +
                "									) b ON a.编号 = b.交车     " +
                "                	LEFT JOIN 报价精品表 u ON b.精品=u.编号    " +
                "                	LEFT JOIN 报价精品详细表 p ON p.编号 =u.编号    " +
                "                	LEFT JOIN LSH精品套餐详细 s ON s.套餐编号=p.ID    " +
                "                	LEFT JOIN (    " +
                "                		SELECT SUM(IFNULL(a.经销商采购价未税,'0')) AS 经销商采购价未税, a.精品编号    " +
                "                        FROM LSH精品信息 a    " +
                "                		WHERE a.经销商销售价含税 = '0'    " +
                "                        OR a.经销商销售价含税 IS NULL    " +
                "                        GROUP BY a.精品编号    " +
                "                	) v ON v.精品编号 = s.精品编号    " +
                "                	LEFT JOIN (    " +
                "                		SELECT SUM(随车成本) AS 经销商赠送成本, 报价编号    " +
                "                		FROM 报价随车表    " +
                "                		WHERE 随车类型 = '经销商' AND 随车部门='销售部'    " +
                "                		GROUP BY 报价编号    " +
                "                	) d ON d.报价编号 = b.报价编号    " +
                "                	LEFT JOIN (    " +
                "                		SELECT SUM(随车成本) AS 厂家随车SI成本, 报价编号    " +
                "                		FROM 报价随车表    " +
                "                		WHERE 随车类型 = '厂家'    " +
                "                		AND 随车类型 <> '购置税' AND 随车部门='销售部'    " +
                "                		GROUP BY 报价编号    " +
                "                	) e ON e.报价编号 = b.报价编号    " +
                "                	LEFT JOIN(    " +
                "                		SELECT    " +
                "                		SUM(IFNULL(厂家返利,0)) AS 厂家返利,报价编号    " +
                "                		FROM 报价随车表    " +
                "                		WHERE 厂家返利 IS NOT NULL    " +
                "                		GROUP BY 报价编号    " +
                "                	) m ON m.报价编号=b.报价编号    " +
                "									LEFT JOIN(  " +
                "														select SUM(IFNULL(a.BMBS_retail_SI,0)) as BMBS_retail_SI,  " +
                "																	SUM(IFNULL(a.BMBS_wholesale_SI,0)) as BMBS_wholesale_SI,a.carCode,a.permit from snp_setting a GROUP BY a.carCode,a.permit  " +
                "									)	ss ON ss.carCode=b.车型编号 and ss.permit=b.许可						  " +
                "                	LEFT JOIN (    " +
                "                		SELECT    " +
                "                		延保编号,    " +
                "                		IFNULL(延保原价,0) as 延保原价  " +
                "                		FROM    " +
                "                		报价五星延保    " +
                "                		WHERE    " +
                "                		延保折后价 = 0    " +
                "                	) g ON g.延保编号 = b.延保编号    " +
                "                	LEFT JOIN (    " +
                "                		SELECT    " +
                "                		延保编号,    " +
                "                		(IFNULL(延保折后价,0)-IFNULL(延保原价,0)) AS 五星毛利    " +
                "                		FROM    " +
                "                		报价五星延保    " +
                "                		WHERE    " +
                "                		延保折后价 <> 0 and 延保折后价 IS NOT NULL    " +
                "                	) n ON n.延保编号 = b.延保编号    " +
                "                	LEFT JOIN (    " +
                "                		SELECT a.费用编号, SUM(b.费用折后价) AS 其他服务费    " +
                "                		FROM 报价费用表 a    " +
                "                		JOIN 报价费用详细表 b ON b.费用编号 = a.费用编号    " +
                "                		WHERE b.费用名称 = '其他服务费'    " +
                "                		GROUP BY a.费用编号    " +
                "                	) h ON h.费用编号 = b.费用编号    " +
                "  " +
                "                	LEFT JOIN (    " +
                "                		SELECT a.费用编号, SUM(b.费用折后价) AS 上牌服务费    " +
                "                		FROM 报价费用表 a    " +
                "                		JOIN 报价费用详细表 b ON b.费用编号 = a.费用编号    " +
                "                		WHERE b.费用名称 = '上牌服务费'    " +
                "                		GROUP BY a.费用编号    " +
                "                	) hh ON hh.费用编号 = b.费用编号  " +
                "  " +
                "                	LEFT JOIN 报价金融表 i ON i.金融编号 = b.金融    " +
                "                	LEFT JOIN 报价保险表 j ON j.保险编号 = b.保险编号 AND j.投保类型 = '新保'    " +
                "                	LEFT JOIN 报价置换表 k ON k.置换编号 = b.置换编号    " +
                "                	LEFT JOIN (    " +
                "												select SUM(IFNULL(a.`折后价`,0)) AS 折后价,IFNULL(SUM(a.成本),0) AS 成本 ,b.编号  from 报价精品详细表 a   " +
                "												JOIN `报价精品表` b ON a.`编号`=b.编号 GROUP BY a.`编号`   " +
                "                	) l ON l.编号 = b.精品    " +
                "                	LEFT JOIN (    " +
                "                		SELECT si_adjust,finance_poundage_adjust, insurance_poundage_adjust,after_market_product_cost_adjust, five_stars_extend_cost_adjust,stock_code    " +
                "                		FROM income_adjustment    " +
                "                	) r ON b.库存编号 = r.stock_code    " +
                "                	WHERE 1 = 1)t ";
        Map<String, String> map = Maps.newHashMap();
        map.put("vin", vin);
        List data = this.getList(sql, map);
        Object obj = Iterables.getFirst(data, null);
        if (obj == null) {
            gpMap.put("SalesTGP", "");
            gpMap.put("SalesTGPRate", "");
            gpMap.put("GP1", "");
            gpMap.put("GP1Rate", "");
            gpMap.put("GP2", "");
            gpMap.put("GP2Rate", "");
            gpMap.put("GP3", "");
            gpMap.put("GP3Rate", "");
        } else {
            Map m = (Map) obj;
            gpMap.put("SalesTGP", m.get("SalesTGP"));
            gpMap.put("SalesTGPRate", m.get("SalesTGPRate"));
            gpMap.put("GP1", m.get("GP1"));
            gpMap.put("GP1Rate", m.get("GP1Rate"));
            gpMap.put("GP2", m.get("GP2"));
            gpMap.put("GP2Rate", m.get("GP2Rate"));
            gpMap.put("GP3", m.get("GP3"));
            gpMap.put("GP3Rate", m.get("GP3Rate"));
        }
        return gpMap;
    }

    @Override
    public List getNowTime() {
        String sql = "select YEAR(now()) 年,MONTH(now()) 月,DAYOFMONTH(now()) 日,HOUR(now()) 时,MINUTE(now()) 分,SECOND(now()) 秒";
        return this.getList(sql);
    }

    @Override
    public List getLatestUpdatetimeByPermit(String permit) {
        String sql = "SELECT MAX(`更新时间`) as 更新时间, `更新账号` FROM `lsh保险设置` WHERE `许可` =:permit";
        Map<String, String> map = Maps.newHashMap();
        map.put("permit", permit);
        return this.getList(sql, map);
    }

    @Override
    public List getBenzRental(String bTime, String eTime, Integer type, String rate, String carType) {
        Map map = Maps.newHashMap();
        map.put("bTime", bTime);
        map.put("eTime", eTime);
        map.put("type", type);
        map.put("rate", rate);
        map.put("carType", carType);
        StringBuffer sql = new StringBuffer(" SELECT\n" +
                "\tb.期数\n" +
                "FROM\n" +
                "\t`lsh金融设置` AS a\n" +
                "LEFT JOIN `lsh先享后选设置` AS b ON a.`金融编号` = b.`金融编号`\n" +
                "LEFT JOIN `lsh金融设置适用车型` AS c ON a.`金融编号` = c.`金融编号`\n" +
                "LEFT JOIN `lsh车型` AS d ON c.`适用车型` = d.`车型编号`\n" +
                "WHERE\n" +
                "\ta.`开始日期` =:bTime \n" +
                "AND a.`结束日期` =:eTime \n" +
                "AND b.`期数` = '24'\n" +
                "AND b.`利率类型` =:type\n" +
                "AND b.`年利率` =:rate\n" +
                "AND  d.`车型编号` =:carType");
        return this.getList(sql.toString(), map);
    }


    @Override
    public PageData getBenzRental(ApiForLiveService.GetBenzRentalRequst req) {
        String sql = "SELECT * FROM (\n" +
                "SELECT d.`品牌`, d.`级别`, d.`车型`, d.`车型详细`, b.`更新时间`,d.MSRP,b.`更新账号`,\n" +
                "  MAX( IF ( a.`利率类型` = '1' AND a.`期数` = '24', a.`年利率`, '' ) )AS `星时享24`,\n" +
                "  MAX(  IF ( a.`利率类型` = '1' AND a.`期数` = '36', a.`年利率`, '' ) )AS `星时享36`, \n" +
                "  MAX( IF ( a.`利率类型` = '0' AND a.`期数` = '24', a.`年利率`, '' ) )AS `普通24`, \n" +
                "  MAX( IF ( a.`利率类型` = '0' AND a.`期数` = '36', a.`年利率`, '' ) )AS `普通36`, \n" +
                "  MAX( IF ( a.`期数` = '24' AND a.`参数` = '10000' , a.`保值比例`, '' ) )AS `比例1`, \n" +
                "  MAX( IF ( a.`期数` = '24' AND a.`参数` = '15000' , a.`保值比例`, '' ) )AS `比例2`, \n" +
                "  MAX( IF ( a.`期数` = '24' AND a.`参数` = '20000' , a.`保值比例`, '' ) )AS `比例3`, \n" +
                "  MAX( IF ( a.`期数` = '24' AND a.`参数` = '25000' , a.`保值比例`, '' ) )AS `比例4`, \n" +
                "  MAX( IF ( a.`期数` = '36' AND a.`参数` = '10000' , a.`保值比例`, '' ) )AS `比例5`, \n" +
                "  MAX( IF ( a.`期数` = '36' AND a.`参数` = '15000' , a.`保值比例`, '' ) )AS `比例6`, \n" +
                "  MAX( IF ( a.`期数` = '36' AND a.`参数` = '20000' , a.`保值比例`, '' ) )AS `比例7`, \n" +
                "  MAX( IF ( a.`期数` = '36' AND a.`参数` = '25000' , a.`保值比例`, '' ) )AS `比例8`, \n" +
                "b.`开始日期`, b.`结束日期`, b.产品编码 as 产品编号,c.`金融编号` \n" +
                "FROM `lsh先享后选设置` AS a \n" +
                "LEFT JOIN `lsh金融设置` AS b ON a.`金融编号` = b.`金融编号` \n" +
                "LEFT JOIN `lsh金融设置适用车型` AS c ON a.`金融编号` = c.`金融编号` \n" +
                "LEFT JOIN `lsh车型` AS d ON c.`适用车型` = d.`车型编号` \n" +
                "WHERE b.`许可` = 'LSH'    GROUP BY a.`金融编号`     " +
                "     ORDER BY b.`更新时间`  ) AS N  WHERE 1=1  ";
        Map map = Maps.newHashMap();
        if (!StringUtils.isEmpty(req.品牌)) {
            sql += " AND (N.`级别` LIKE :brand  OR N.`品牌` LIKE :brand OR N.`车型` LIKE :brand OR N.`车型详细` LIKE :brand)";
            map.put("brand", "%" + req.品牌 + "%");
        }
        if (req.年利率1 != 0) {
            sql += "AND   (N.`星时享24` >=:rate1 )  AND \n" +
                    "  (N.`星时享36`   >=:rate1  )  AND " +
                    "  (N.`普通24`   >=:rate1 ) AND " +
                    "  (N.`普通36`   >=:rate1  )  ";
            map.put("rate1", req.年利率1);
        }
        if (req.年利率2 != 0) {
            sql += " AND   (N.`星时享24` <:rate2 )  AND \n" +
                    "  (N.`星时享36` <:rate2 )  AND " +
                    "  (N.`普通24` <:rate2) AND " +
                    "  (N.`普通36` <:rate2 ) ";
            map.put("rate2", req.年利率2);
        }
        Date sDate=req.开始时间;Date eDate=req.结束时间;
        if(sDate != null && !"".equals(sDate.toString())&&eDate != null && !"".equals(eDate.toString())){

            sql+="AND (" +
                    "    (" +
                    "      N.开始日期 <= '"+sDate+"' " +
                    "      AND N.结束日期 >= '"+eDate+"'   " +
                    "    ) " +
                    "    OR (" +
                    "      N.开始日期 <= '"+sDate+"' " +
                    "      AND N.结束日期 >= '"+sDate+"'  " +
                    "    ) " +
                    "    OR (" +
                    "      N.开始日期 <= '"+eDate+"'  " +
                    "      AND N.结束日期 >= '"+eDate+"'" +
                    "    ) " +
                    "    OR (" +
                    "      N.开始日期 >= '"+sDate+"' " +
                    "      AND N.结束日期 <= '"+eDate+"'   " +
                    "    )" +
                    "  ) ";



        }else {
            if(sDate != null && !"".equals(sDate.toString())){
                sql += " and (N.开始日期 >= '"+sDate+"'  or  N.结束日期>='"+sDate+"' )   \n";
              //  params.put("startDate", sDate);

            }else if(eDate != null && !"".equals(eDate.toString())){
                sql += " and (N.结束日期 <='"+eDate+"' or N.开始日期<= '"+eDate+"'  )   \n";
               // params.put("endDate", eDate);
            }
        }

        sql = sql + " ORDER BY `更新时间` ";

        return this.getPage(sql, map, req.getPageSize(), req.getPageIndex());
    }

    @Override
    public List getCarsByPermit1(List<String> permitList, String lv, Map paramMap) {
        String sql = "SELECT *,if(aa.sum1 is null ,'0', if( aa.sum1/1.17>1300000,aa.sum1*1.1291,aa.sum1) ) as 车辆折后价,   \n" +
                "  if(aa.sum1 is null or (aa.sum1/1.17)<1300000  ,'0',aa.sum1*0.1291)  as  消费税及附加    \n" +
                " FROM (SELECT  a.`实际MSRP含税` as sum1,  a.`生产日期`, IF ( a.`生产号` LIKE '%IRISTechNV_%' OR a.`生产号` LIKE '%IRISTechnologies%', '', a.`生产号` ) `生产号`, \n" +
                "  IF ( a.`车架号` LIKE '%IRISTechNV_%' OR a.`车架号` LIKE '%IRISTechnologies%', '', a.`车架号` ) `车架号（VIN）`, a.`欧版底盘号` `欧版底盘号（FIN)`, \n" +
                " a.`发动机号`, a.`VSB号`, a.`颜色代码`, a.`颜色`, a.`内饰代码`, a.`内饰`, a.`原厂配置`, a.`加配配置`, a.`加配价格含税` `加配价格（含税）`, a.`采购价格含税` `采购价格（含税）`, \n" +
                " a.`采购价格未税` `采购价格（不含税）`, c.`MSRP` `BI车款标配MSRP（含税）`, a.`标配MSRP含税` `标配MSRP（含税）`, a.`实际MSRP含税` `实际MSRP（含税）`, \n" +
                " a.`SNP含税` `标配区域限价-SNP（含税）`, a.`厂家确认订单日期`, a.`预计到港日期` `预计到港日期（ETA）`, a.`实际到港日期` `实际到港日期（ATA）`, a.`预计下线日期`, \n" +
                " a.`港口代码`, a.`港口名称`, a.`车辆物流状态`, a.`车辆零售状态`, a.`车辆零售类型`, a.`HOLD日期`, a.`申请释放日期`, a.`批发日期`, a.`批发付款到期日`, a.`NBI车辆状态`, \n" +
                " a.`预计到店日期`, a.`店内车辆位置`, a.`二次PDI完成日期`, a.`二次PDI结果`, a.`入库日期`, a.`出库日期`, a.`库龄天数`, a.`建议三次PDI日期`, a.`三次PDI完成日期` `三次PDI 完成日期`, \n" +
                " a.`批发日期` 采购日期, a.`三次PDI结果` `三次PDI 结果`, a.`车辆受损情况`, a.`商检单到店状态`, a.`关单到店状态`, a.`合格证到店状态`, a.`合格证号`, a.`商检单号`, a.`关单号`, a.`批发商`, a.`外部可见级别`, \n" +

                "   IF(a.`advance_sales_days` is null,'否','是') 是否提前销售, " +
                "   IF(a.`advance_sales_days` is null,null,`库存利润表`.`上报厂家日期`) 提前销售日期, " +
                "   a.`advance_sales_days` 提前销售天数," +
                "   a.`route_days`  在途天数, " +
                "       (case when (a.`生产号` NOT LIKE '%IRISTechNV_%' OR a.`生产号` NOT LIKE '%IRISTechnologies%') and NBI车辆状态 = '在途库存'and 车辆零售状态='未卖' then '在途可售' \n" +
                "           when (a.`生产号` NOT LIKE '%IRISTechNV_%' OR a.`生产号` NOT LIKE '%IRISTechnologies%') and NBI车辆状态 = '现货库存'and 车辆零售状态='未卖' then '在库可售'\n" +
                "           when (a.`生产号` NOT LIKE '%IRISTechNV_%' OR a.`生产号` NOT LIKE '%IRISTechnologies%') and 报价交车表.开票时间 is not null and 报价交车表.`交车日期` is null then '已开票未提车'\n" +
                "           when (a.`生产号` NOT LIKE '%IRISTechNV_%' OR a.`生产号` NOT LIKE '%IRISTechnologies%') and NBI车辆状态 = '在途库存' and (车辆零售状态='已卖' or 报价订单表.`订单申请日期` is not null ) then '在途已配'\n" +
                "           when (a.`生产号` NOT LIKE '%IRISTechNV_%' OR a.`生产号` NOT LIKE '%IRISTechnologies%') and NBI车辆状态 = '现货库存'and (车辆零售状态='已卖' or 报价订单表.`订单申请日期` is not null ) then '在库已配' \n" +
                "   end)    车辆库存状态,  " +
                "   a.car_number 车辆编号,  " +
                "   a.warehouse 仓库,  " +
                "   a.inventory_location 库存位置,  " +
                "   a.remark 备注,  " +
                
                " b.`经销商名` AS 所属经销商, c.`品牌` `品牌`, c.`来源`, c.`级别` AS 车系, c.车型详细 AS 车款, c.车型, ' ' `采购经销商`, ' ' `内部可见级别`, ' ' `上牌类型`, ' ' `订单客户`, \n" +
                " ' ' `顾客email`, ' ' `顾客身份证号码`, ' ' `顾客外籍ID号码`, ' ' `组织机构代码`, ' ' `置换车辆上牌年月`, ' ' `车辆落地价（不含购置税）`, ' ' `车辆现金折扣`, ' ' `车辆销售价`, \n" +
                " ' ' `置换估价`, ' ' `五星延保MSRP（含税）`, ' ' `五星延保实收金额（含税）`, ' ' `五星延保成本（含税）`, ' ' `Case号`, ' ' `订单号`, ' ' `意向金金额`, ' ' `订金金额`, \n" +
                " ' ' `意向金无息退款天数`, ' ' `释放资源期限`, ' ' `申请修改原因`, ' ' `撤销订单原因`, ' ' `车辆尾款（顾客）`, ' ' `代付款`, ' ' `应收金额`, ' ' `实收金额`, ' ' `销售价格`, \n" +
                " ' ' `不含税价`, ' ' `订单审批通过日期`, ' ' `订单签署日期`, ' ' `订金交付日期`, ' ' `订单失销日期`, ' ' `退订金金额`, ' ' `退订金转账日期`, ' ' `订单修改日期`, ' ' `订金+/-金额`, \n" +
                " ' ' `订金+/-日期`, ' ' `合同签署日期`, ' ' `开票日期`, ' ' `合同金额`, ' ' `余款金额`, ' ' `合同交货日期`, ' ' `交车日期`, ' ' `上牌省份`, ' ' `上牌城市`, ' ' `上牌行政区`, \n" +
                " ' ' `上牌具体地址`, ' ' `上牌日期`, ' ' `牌照号`, ' ' `是否置换`, ' ' `店内置换/店外置换`, ' ' `二手车价格`, ' ' `车主/开票人名称`, ' ' `车主/开票人身份证号码`, \n" +
                " ' ' `外籍车主车主/开票人ID号码`, ' ' `车主/开票人组织机构代码`, ' ' `车主/开票人电话号码`, ' ' `提车代理人`, ' ' `提车代理人身份证`, ' ' `外籍提车代理人ID号码`, \n" +
                " ' ' `售后联系人1`, ' ' `售后联系人1身份`, ' ' `售后联系人1电话号码`, ' ' `售后联系人1联系地址`, ' ' `售后联系人1微信ID`, ' ' `上报厂家`, ' ' `上报厂家日期`, ' ' `上报SIMS`, \n" +
                " ' ' `上报SIMS日期`, ' ' `GP1`, ' ' `GP1%`, ' ' `经销商随车成本`, ' ' `厂家SI_保险成本`, ' ' `厂家SI_购置税成本`, ' ' `厂家SI_SSSC成本`, ' ' `厂家SI_旅游礼包成本`, \n" +
                " ' ' `厂家SI_SD卡成本`, ' ' `厂家SI_加油卡成本`, ' ' `厂家SI_实物礼品成本`, ' ' `厂家SI_服务礼券成本`, ' ' `GP2`, ' ' `GP2%`, ' ' `厂家SI_保险实收`, ' ' `厂家SI_购置税实收`, \n" +
                " ' ' `厂家SI_SSSC实收`, ' ' `厂家SI_旅游礼包实收`, ' ' `厂家SI_SD卡实收`, ' ' `厂家SI_加油卡实收`, ' ' `厂家SI_实物礼品实收`, ' ' `厂家SI_服务礼券实收`, ' ' `厂家零售SI`, \n" +
                " ' ' `厂家批售SI`, ' ' `GP3`, ' ' `GP3%`, ' ' `上牌服务费`, ' ' `其他服务费`, ' ' `现金销售五星延保毛利`, ' ' `现金销售后市场产品毛利`, ' ' `后市场毛利`, ' ' `金融手续费`, \n" +
                " ' ' `新保手续费`, ' ' `置换手续费`, ' ' `Sales T.GP`, ' ' `Sales T.GP%`, ' ' `销售顾问`, a.库存编号 \n" +
                "  FROM LSH库存表 a LEFT JOIN `lsh经销商表` b ON a.`所属经销商` = b.`许可_BMBS` LEFT JOIN LSH车型 c ON a.车型编号 = c.车型编号\n" +
                " LEFT JOIN  (     " +
                " select a.*        " +
                "     FROM (      " +
                " 	    SELECT a.* , b.`订单状态` 订单状态   " +
                " 		    FROM 报价单表 a , 报价订单表  b    " +
                " 	    where A.许可=:permit and a.订单编号=b.`订单编号`    " +
                " 	    and 是否驳回 = '否'  order by  `报价单号` desc    " +
                " 		) a group by a.初始报价单号    " +
                "    ) 报价单表 ON `报价单表`.`库存编号`=  a.库存编号     " +
                " and 报价单表.订单状态 in ( '意向订单O','意向订单C', '订单修改' , '订单C' , '订单O' , '合同O' , '合同C' , '开票O' , '开票C' , '换票C' , '换票O' , '退票O' , '合同修改' , '换票','退订','退订R' )    \n" +
                "   LEFT JOIN 顾客记录汇总 ON 顾客记录汇总.顾客编号=报价单表.`顾客编号`   " +
                " LEFT JOIN  `报价金融表` ON `报价金融表`.`金融编号` = `报价单表`.`金融`     " +
                " LEFT JOIN `报价订单表` ON `报价单表`.`订单编号` = `报价订单表`.`订单编号`    " +
                " LEFT JOIN `报价交车表` ON `报价单表`.`交车` = `报价交车表`.`编号`    " +
                " LEFT JOIN `库存利润表` ON a.`库存编号` = `库存利润表`.`库存编号` " +
                "  WHERE a.`车辆零售状态` = '未卖' AND a.`所属经销商` in " ;

        Map map = Maps.newHashMap();
        map.put("permit",permitList.get(0).toString());
        String permit ="";
        for(int a=0;a<permitList.size();a++){
            if (a==0){
                permit=permit+"'"+permitList.get(a)+"'";}
            else {
                permit=permit+", "+"'"+permitList.get(a)+"'";
            }
        }
        permit ="("+permit+")";

        Iterator<Map.Entry<String, String>> it = paramMap.entrySet().iterator();
        sql  = sql+permit+" ) aa \n" +
                "WHERE 1=1  ";
        if ("全国可见".equals(lv)) {
            sql += " and  aa.外部可见级别='全国可见' ";
        }
        if ("全国大区可见".equals(lv)) {
            sql += " and (aa.外部可见级别='全国可见' OR aa.外部可见级别='大区可见') ";
        }
        if ("全国大区小区可见".equals(lv)) {
            sql += " and (aa.外部可见级别='全国可见' OR aa.外部可见级别='大区可见' OR aa.外部可见级别='小区可见') ";
        }
        if ("外部不可见".equals(lv)) {
            sql += " and (aa.外部可见级别 <> '外部不可见')  ";
        }
        while (it.hasNext()) {
            Map.Entry<String, String> entry = it.next();
            //过滤掉前端传回的参数map中 "1":"1" 的情况
            if ("1".equals(entry.getKey()) || "param".equals(entry.getKey())) {
                continue;
            }
            sql += " AND aa.`" + entry.getKey() + "` like '%" + entry.getValue() + "%' ";
        }
        sql += " ORDER BY  aa.`批发日期`DESC, aa.`生产日期` DESC, aa.`生产号` DESC";
        return this.getList(sql, map);
    }

    @Override
    public List getAllArea(String permit) {
        String sql = "SELECT `大区`,`小区`,CTA, `经销商名`, `许可_BMBS` FROM `LSH经销商表`";
        return this.getList(sql);
    }

    @Override
    public List getCusRecordByPhone(ApiForLiveService.GetCusRecordByPhoneReq req) {
        String sql = "SELECT * FROM `顾客记录汇总` WHERE `电话号码` =:phone AND `许可` =:permit";
        Map map = Maps.newHashMap();
        map.put("permit", req.permit);
        map.put("phone", req.phone);
        return this.getList(sql, map);
    }

    @Override
    public List getCusFlowByPermitAndDate(String license, String currDate, String firstDate, String lastDate) {
        Map map = Maps.newHashMap();
        String cusFlowSql = "SELECT COUNT(*) 客流 \n" +
                "FROM `顾客进店记录表` \n" +
                "WHERE ( `到店时间` <> '00:00' AND `到店时间` <> '1:00' AND `到店时间` <> '3:00' ) \n" +
                "AND ( `到店目的` = '看车比价' OR `到店目的` = '订单' OR ( `到店目的` IN ('办手续','交车') AND `是否订单` = '是' ))\n" +
                "AND 许可=:permit ";
        map.put("permit", license);
        if (currDate != null) {
            cusFlowSql += "AND `进店日期`=:currDate";
            map.put("currDate", currDate);

        } else {
            cusFlowSql += "AND `进店日期` <=:lastDate AND `进店日期` >=:firstDate";
            map.put("firstDate", firstDate);
            map.put("lastDate", lastDate);
        }
        return this.getList(cusFlowSql, map);
    }

    @Override
    public List getClueByPermitAndDate(String license, String currDate, String firstDate, String lastDate) {
        Map map = Maps.newHashMap();
        String clueSql = "SELECT COUNT(DISTINCT(a.顾客编号)) AS 进店线索数 \n" +
                "FROM 顾客进店记录表 a \n" +
                "WHERE a.许可 =:permit \n" +
                "AND ( a.`到店目的` = '看车比价' OR a.`到店目的` = '订单' OR ( a.`到店目的` = '交车' AND a.`是否订单` = '是' )) \n" +
                "AND a.顾客编号 IS NOT NULL ";
        map.put("permit", license);
        if (currDate != null) {
            clueSql += " AND a.`进店日期` =:currDate ";
            map.put("currDate", currDate);
        } else {
            clueSql += " AND `进店日期` <=:lastDate AND `进店日期` >=:firstDate ";
            map.put("firstDate", firstDate);
            map.put("lastDate", lastDate);
        }
        return this.getList(clueSql, map);
    }

    @Override
    public List getOrderByPermitAndDate(String license, String currDate, String firstDate, String lastDate) {
        Map map = Maps.newHashMap();
        String orderSql = "SELECT  COUNT(DISTINCT   bjd.`订单号` ) 新增订单数\n" +
                "                 FROM  `lsh车型`  AS cx \n" +
                "                 JOIN `报价单表`  AS  bj ON  cx.`车型编号`=bj.`车型编号`  \n" +
                "                 JOIN  `报价订单表` AS  bjd ON  bj.`订单编号`=bjd.`订单编号`   \n" +
                "                 WHERE  bj.`许可`=:permit \n" +
                "                 AND  bjd.`订单状态`<> '退订'  \n" +
                "                 AND  bjd.`订单状态` NOT LIKE '%意向订单%' " +
                "                 AND bjd.`订单号` IS NOT NULL \n" +
                "                 AND  bjd.订金类型= '订金' ";
        map.put("permit", license);
        if (currDate != null) {
            orderSql += " AND bjd.`订单签署日期`=:currDate ";
            map.put("currDate", currDate);
        } else {
            orderSql += "AND bjd.`订单签署日期` <=:lastDate AND bjd.`订单签署日期` >=:firstDate ";
            map.put("firstDate", firstDate);
            map.put("lastDate", lastDate);
        }
        return this.getList(orderSql, map);
    }

    @Override
    public ResultData getAllAreaAndPMA() {
        String sql = "SELECT `大区`,CTA, `经销商名`, `许可_BMBS` FROM `LSH经销商表`";
        List list=this.getList(sql);
        return new ResultData("true","查询成功",new PageData(list,list.size()));
    }

    @Override
    public Map getGroupRoleInInfo(String role) {
        String sql = "select a.roleLevel from lsh_group_role_map a where a.roleName=:role";
        Map<String,String> params = new HashMap<>();
        params.put("role",role);
        List<Map> resultSet =  this.getList(sql,params);
        if(resultSet != null && resultSet.size() > 0){
            return resultSet.get(0);
        }else{
            return null;
        }
    }

    @Override
    public List getCustomers(CustomerInfoService.customersRequest cust) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT * FROM ( ");
        sb.append(" SELECT ");
        sb.append(" 	`电话号码`,  ");
        sb.append(" 	`顾客姓名`, ");
        sb.append(" 	`新车销售状态`, ");
        sb.append(" 	concat('1-',`顾客编号`) 顾客编号 ");
        sb.append(" FROM `顾客记录汇总` ");
        sb.append(" WHERE `顾客记录汇总`.`许可` = :permit ");
        sb.append(" AND IFNULL(`顾客记录汇总`.`顾客状态`,'') <> '战败' ");
        sb.append(" AND `顾客记录汇总`.`所属销售顾问` = :scCode ");
        sb.append(" UNION ALL  ");
        sb.append(" SELECT  ");
        sb.append(" 	`电话号码` ,  ");
        sb.append(" 	`顾客姓名` , ");
        sb.append(" 	`新车销售状态` , ");
        sb.append(" 	concat('2-',`导入编号`) 顾客编号 ");
        sb.append(" FROM `lsh导入名单`   ");
        sb.append(" WHERE `许可` = :permit   ");
        sb.append(" AND `所属销售顾问` = :scCode   ");
        sb.append(" AND `是否线索` = '是'   ");
        sb.append(" AND `是否有效线索` = '是'   ");
        sb.append(" AND `线索创建日期` >= date_sub( date_sub( date_format(now(), '%y-%m-%d'), INTERVAL extract(DAY FROM now()) - 1 DAY ), INTERVAL 2 MONTH )  ");
        sb.append(" AND `电话号码` NOT IN (SELECT `电话号码` from `顾客记录汇总` WHERE `许可` = :permit) ");
        sb.append(" ) aa WHERE 1=1   ");
        Map map =  Maps.newHashMap();
        map.put("permit", cust.permit);
        map.put("scCode", cust.scCode);
        if (!Strings.isNullOrEmpty(cust.custName)){
            sb.append(" AND aa.顾客姓名 LIKE :custName ");
            map.put("custName", "%" + cust.custName + "%");
        }
        return this.getList(sb.toString(), map);
    }
}
